package cn.nawang.ebeim.client.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.nawang.ebeim.client.bean.DownloadProcessBarBean;
import cn.nawang.ebeim.client.bean.UploadProcessBarBean;

public class DbUpDownloadUtil {
  public static final int STATUS_NEW = 0;
  public static final int STATUS_RUNNING = 1;
  public static final int STATUS_FINISHED = 2;
  public static final int STATUS_ERROR = 3;
  
  public static final int PROCESS_BAR_STATUS_RUNNING = 1;
  public static final int PROCESS_BAR_STATUS_SUCCESS = 2;
  

  private static final String SQL_TABLE_DOWNLOAD_PROCESS_BAR = "create table if not exists download_process_bar("
      + "id integer primary key autoincrement, ds_id varchar(50),total number,count number,status integer,nid varchar(50),file_sum integer,ok_sum integer,error_sum integer)";
  private static final String SQL_TABLE_UPLOAD_PROCESS_BAR = "create table if not exists upload_process_bar("
      + "id integer primary key autoincrement, ds_id varchar(50),total number,count number,status integer,name varchar(255),ds_type varchar(1),file_sum integer,ok_sum integer,error_sum integer,user_id varchar(255))";
 
  private static final String SQL_INSERT_DOWNLOAD_PROCESS_BAR = "insert into download_process_bar(ds_id,total,count,status,nid,file_sum,ok_sum,error_sum) values(?,?,?, ?,?,?, ?,?)";
  private static final String SQL_INSERT_UPLOAD_PROCESS_BAR = "insert into upload_process_bar(ds_id,total,count,status,name,ds_type,file_sum,ok_sum,error_sum,user_id) values(?,?,?,?, ?,?,?,?,?,?)";

  private static final String SQL_SELECT_DOWNLOAD_PROCESS_BAR_BY_NID =
      "select id,ds_id,total,count,status,nid,file_sum,ok_sum,error_sum"
      + " from download_process_bar where status = ? and nid = ? order by id asc";
  private static final String SQL_SELECT_UPLOAD_PROCESS_BAR =
      "select id,ds_id,total,count,status,name,ds_type,file_sum,ok_sum,error_sum"
      + " from upload_process_bar where ds_id=? and status = ? order by id asc";
  private static final String SQL_SELECT_UPLOAD_PROCESS_BAR_ALL =
      "select id,ds_id,total,count,status,name,ds_type,file_sum,ok_sum,error_sum"
      + " from upload_process_bar where user_id = ? and ds_type = ? order by id desc";
  private static String SQL_SELECT_UPLOAD_PROCESS_BARS =
      "select id,ds_id,total,count,status,name,ds_type,file_sum,ok_sum,error_sum"
      + " from upload_process_bar where ds_id in (DSIDS) and status = ? order by id asc";
  private static String SQL_SELECT_DOWNLOAD_PROCESS_BARS =
      "select id,ds_id,total,count,status,nid,file_sum,ok_sum,error_sum"
      + " from download_process_bar where nid in (NIDS) and status = ? order by id asc";
  
  private static final String SQL_UPDATE_DOWNLOAD_PROCESS_BAR_STATUS_BY_NID = "update download_process_bar set status=? where nid=?";
  private static final String SQL_UPDATE_DOWNLOAD_PROCESS_BAR_COUNT_BY_NID = "update download_process_bar set count=? where nid=?";
  private static final String SQL_UPDATE_DOWNLOAD_PROCESS_BAR_OK_SUM = "update download_process_bar set ok_sum=ok_sum+1 where nid=?";
  private static final String SQL_UPDATE_DOWNLOAD_PROCESS_BAR_ERROR_SUM = "update download_process_bar set error_sum=error_sum+1 where nid=?";
  private static final String SQL_UPDATE_UPLOAD_PROCESS_BAR_STATUS = "update upload_process_bar set status=? where ds_id=?";
  private static final String SQL_UPDATE_UPLOAD_PROCESS_BAR_COUNT = "update upload_process_bar set count=? where ds_id=?";
  private static final String SQL_UPDATE_UPLOAD_PROCESS_BAR_OK_SUM = "update upload_process_bar set ok_sum=ok_sum+1 where ds_id=?";
  private static final String SQL_UPDATE_UPLOAD_PROCESS_BAR_ERROR_SUM = "update upload_process_bar set error_sum=error_sum+1 where ds_id=?";
  
  private static final String SQL_DELETE_DOWNLOAD_PROCESS_BAR_BY_NID = "delete from download_process_bar where nid=?";
  private static final String SQL_DELETE_UPLOAD_PROCESS_BAR = "delete from upload_process_bar where ds_id=?";

  private static Connection connection = null;
  private static boolean isInitTable = false;
  
  public static synchronized void initConnection() throws SQLException {
    if(connection == null){
        String db = FileUtil.getUpDownloadDatabase();
      connection = DriverManager.getConnection("jdbc:sqlite:" + db);
    }
  }

  
  
  public static synchronized void initDatabase() throws SQLException {
    
    if(isInitTable){
        return;
    }
    
    Statement stmt = null;
    try {
        stmt = connection.createStatement();
        stmt.execute(SQL_TABLE_DOWNLOAD_PROCESS_BAR);
        stmt.execute(SQL_TABLE_UPLOAD_PROCESS_BAR);
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        if(stmt!=null){
            stmt.close();
        }
    }
    isInitTable = true;
 
  }
  
  public static void deleteDownloadProcessBarByNid(String nid) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_DELETE_DOWNLOAD_PROCESS_BAR_BY_NID);
      stmt.setString(1, nid);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  public static void deleteUploadProcessBar(String dsId) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_DELETE_UPLOAD_PROCESS_BAR);
      stmt.setString(1, dsId);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  
  public static synchronized void updateDownloadProcessBarStatusByNid(int status,String nid) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_UPDATE_DOWNLOAD_PROCESS_BAR_STATUS_BY_NID);
      stmt.setInt(1, status);
      stmt.setString(2, nid);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  public static synchronized void updateUploadProcessBarStatus(String dsId,int status) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_UPDATE_UPLOAD_PROCESS_BAR_STATUS);
      stmt.setInt(1, status);
      stmt.setString(2, dsId);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  public static synchronized void updateUploadProcessBarOkSum(String dsId) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_UPDATE_UPLOAD_PROCESS_BAR_OK_SUM);
      stmt.setString(1, dsId);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  public static synchronized void updateUploadProcessBarErrorSum(String dsId) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_UPDATE_UPLOAD_PROCESS_BAR_ERROR_SUM);
      stmt.setString(1, dsId);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  public static synchronized void updateDownloadProcessBarCountByNid(long count,String nid) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_UPDATE_DOWNLOAD_PROCESS_BAR_COUNT_BY_NID);
      stmt.setLong(1, count);
      stmt.setString(2, nid);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  public static synchronized void updateDownloadProcessBarOkSum(String nid) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_UPDATE_DOWNLOAD_PROCESS_BAR_OK_SUM);
      stmt.setString(1, nid);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  public static synchronized void updateDownloadProcessBarErrorSum(String nid) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_UPDATE_DOWNLOAD_PROCESS_BAR_ERROR_SUM);
      stmt.setString(1, nid);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  public static synchronized void updateUploadProcessBarCount(String dsId,long count) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_UPDATE_UPLOAD_PROCESS_BAR_COUNT);
      stmt.setLong(1, count);
      stmt.setString(2, dsId);
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  
  public static void addDownloadProcessBar(DownloadProcessBarBean bean) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_INSERT_DOWNLOAD_PROCESS_BAR);
      stmt.setString(1,  bean.getDsId());
      stmt.setLong(2,  bean.getTotal());
      stmt.setLong(3, bean.getCount());
      stmt.setInt(4, bean.getStatus());
      stmt.setString(5, bean.getNid());
      stmt.setInt(6, bean.getFileSum());
      stmt.setInt(7, bean.getOkSum());
      stmt.setInt(8, bean.getErrorSum());
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  
  public static synchronized void addUploadProcessBar(UploadProcessBarBean bean) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_INSERT_UPLOAD_PROCESS_BAR);
      stmt.setString(1,  bean.getDsId());
      stmt.setLong(2,  bean.getTotal());
      stmt.setLong(3, bean.getCount());
      stmt.setInt(4, bean.getStatus());
      stmt.setString(5, bean.getName());
      stmt.setString(6, bean.getDsType());
      stmt.setInt(7, bean.getFileSum());
      stmt.setInt(8, bean.getOkSum());
      stmt.setInt(9, bean.getErrorSum());
      stmt.setString(10, bean.getUserID());
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  
  public static synchronized DownloadProcessBarBean getDownloadProcessBarByNid(int status,String nid) {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    DownloadProcessBarBean bean = null;
    try {
      stmt = connection.prepareStatement(SQL_SELECT_DOWNLOAD_PROCESS_BAR_BY_NID);
      stmt.setInt(1, status);
      stmt.setString(2, nid);
      rs = stmt.executeQuery();
      while (rs.next()) {
        bean = new DownloadProcessBarBean();
        int _id= rs.getInt("id");
        String _dsId= rs.getString("ds_id");
        Long _total= rs.getLong("total");
        Long _count= rs.getLong("count");
        int _status= rs.getInt("status");
        String _nid = rs.getString("nid");
        int fileSum = rs.getInt("file_sum");
        int okSum = rs.getInt("ok_sum");
        int errorSum = rs.getInt("error_sum");
        bean.setId(_id);
        bean.setDsId(_dsId);
        bean.setStatus(_status);
        bean.setTotal(_total);
        bean.setCount(_count);
        bean.setNid(_nid);
        bean.setFileSum(fileSum);
        bean.setOkSum(okSum);
        bean.setErrorSum(errorSum);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }
    return bean;
  }
  
  public static synchronized UploadProcessBarBean getUploadProcessBar(String dsId,int status) {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    UploadProcessBarBean bean = null;
    try {
      stmt = connection.prepareStatement(SQL_SELECT_UPLOAD_PROCESS_BAR);
      stmt.setString(1, dsId);
      stmt.setInt(2, status);
      rs = stmt.executeQuery();
      while (rs.next()) {
        bean = new UploadProcessBarBean();
        int _id= rs.getInt("id");
        String _dsId= rs.getString("ds_id");
        Long _total= rs.getLong("total");
        Long _count= rs.getLong("count");
        int _status= rs.getInt("status");
        String name = rs.getString("name");
        String dsType = rs.getString("ds_type");
        int fileSum = rs.getInt("file_sum");
        int okSum = rs.getInt("ok_sum");
        int errorSum = rs.getInt("error_sum");
        bean.setId(_id);
        bean.setDsId(_dsId);
        bean.setStatus(_status);
        bean.setTotal(_total);
        bean.setCount(_count);
        bean.setName(name);
        bean.setDsType(dsType);
        bean.setFileSum(fileSum);
        bean.setOkSum(okSum);
        bean.setErrorSum(errorSum);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }
    return bean;
  }
  
  public static synchronized List<UploadProcessBarBean> getUploadProcessBarAll(String dsType,String userID) {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    UploadProcessBarBean bean = null;
    List<UploadProcessBarBean> list = new ArrayList<UploadProcessBarBean>();
    try {
      stmt = connection.prepareStatement(SQL_SELECT_UPLOAD_PROCESS_BAR_ALL);
      stmt.setString(1, userID);
      stmt.setString(2, dsType);
      rs = stmt.executeQuery();
      while (rs.next()) {
        bean = new UploadProcessBarBean();
        int _id= rs.getInt("id");
        String _dsId= rs.getString("ds_id");
        Long _total= rs.getLong("total");
        Long _count= rs.getLong("count");
        int _status= rs.getInt("status");
        String name = rs.getString("name");
        String _dsType = rs.getString("ds_type");
        int fileSum = rs.getInt("file_sum");
        int okSum = rs.getInt("ok_sum");
        int errorSum = rs.getInt("error_sum");
        bean.setId(_id);
        bean.setDsId(_dsId);
        bean.setStatus(_status);
        bean.setTotal(_total);
        bean.setCount(_count);
        bean.setName(name);
        bean.setDsType(_dsType);
        bean.setFileSum(fileSum);
        bean.setOkSum(okSum);
        bean.setErrorSum(errorSum);
        list.add(bean);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }
    return list;
  }
  
  public static synchronized List<UploadProcessBarBean> getUploadProcessBars(String dsIds,int status) {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    UploadProcessBarBean bean = null;
    String inIds = "";
    String[] arr = dsIds.split(",");
    for (int i = 0; i < arr.length; i++) {
      inIds += "'" + arr[i] + "',";
    }
    if(inIds.length()>0){
      inIds = inIds.substring(0, inIds.length() - 1);
    }else{
      inIds = "'0'";
    }
    String sql = SQL_SELECT_UPLOAD_PROCESS_BARS.replace("DSIDS", inIds);
    List<UploadProcessBarBean> list = new ArrayList<UploadProcessBarBean>();
    try {
      stmt = connection.prepareStatement(sql);
      stmt.setInt(1, status);
      rs = stmt.executeQuery();
      while (rs.next()) {
        bean = new UploadProcessBarBean();
        int _id= rs.getInt("id");
        String _dsId= rs.getString("ds_id");
        Long _total= rs.getLong("total");
        Long _count= rs.getLong("count");
        int _status= rs.getInt("status");
        String name = rs.getString("name");
        String dsType = rs.getString("ds_type");
        int fileSum = rs.getInt("file_sum");
        int okSum = rs.getInt("ok_sum");
        int errorSum = rs.getInt("error_sum");
        bean.setId(_id);
        bean.setDsId(_dsId);
        bean.setStatus(_status);
        bean.setTotal(_total);
        bean.setCount(_count);
        bean.setName(name);
        bean.setDsType(dsType);
        bean.setFileSum(fileSum);
        bean.setOkSum(okSum);
        bean.setErrorSum(errorSum);
        list.add(bean);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }
    return list;
  }
  
  public static synchronized List<DownloadProcessBarBean> getDownloadProcessBars(String nids,int status) {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    DownloadProcessBarBean bean = null;
    String inIds = "";
    String[] arr = nids.split(",");
    for (int i = 0; i < arr.length; i++) {
      inIds += "'" + arr[i] + "',";
    }
    if(inIds.length()>0){
      inIds = inIds.substring(0, inIds.length() - 1);
    }else{
      inIds = "'0'";
    }
    String sql = SQL_SELECT_DOWNLOAD_PROCESS_BARS.replace("NIDS", inIds);
    List<DownloadProcessBarBean> list = new ArrayList<DownloadProcessBarBean>();
    try {
      stmt = connection.prepareStatement(sql);
      stmt.setInt(1, status);
      rs = stmt.executeQuery();
      while (rs.next()) {
        bean = new DownloadProcessBarBean();
        int _id= rs.getInt("id");
        String _dsId= rs.getString("ds_id");
        Long _total= rs.getLong("total");
        Long _count= rs.getLong("count");
        int _status= rs.getInt("status");
        String _nid = rs.getString("nid");
        int fileSum = rs.getInt("file_sum");
        int okSum = rs.getInt("ok_sum");
        int errorSum = rs.getInt("error_sum");
        bean.setId(_id);
        bean.setDsId(_dsId);
        bean.setStatus(_status);
        bean.setTotal(_total);
        bean.setCount(_count);
        bean.setNid(_nid);
        bean.setFileSum(fileSum);
        bean.setOkSum(okSum);
        bean.setErrorSum(errorSum);
        list.add(bean);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }
    return list;
  }
  
  

  public static void close(PreparedStatement stmt, ResultSet rs) {
    if (rs != null) {
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    if (stmt != null) {
      try {
        stmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  public static void close(PreparedStatement stmt) {
    if (stmt != null) {
      try {
        stmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  public static void close() {

    if (connection != null) {
      try {
        connection.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }


}
